﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using LibUtilities.dal;

namespace CLibArtreve.dal
{
    public class UserDAL : SqlDatabase
    {
        #region CONSTRUCTORS
        public UserDAL(string connectionString) : base(connectionString) { }
        #endregion

        #region OVERRIDE METHODS
        public override string ToString()
        {
            return "DATA ACCES LAYER: " + this.GetType().ToString();
        }
        #endregion

        #region GET METHODS

        public DataSet GetAllUsers()
        {
            try
            {
                return ExecuteDataSetWithCommonQuery(Queries.SELECTALLUSERS, null);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataSet GetUserById(Int64? id)
        {
            try
            {
                // MAKE A LIST OF PARAMETERS
                List<SqlParameter> parameters = new List<SqlParameter>();

                // MAKE PARAMETER
                SqlParameter parameter = new SqlParameter();
                parameter.DbType = DbType.Int64;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_id";
                parameter.Value = id;
                parameters.Add(parameter);

                return ExecuteDataSetWithCommonQuery(Queries.SELECTUSERBYID, parameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataSet GetUserByEmail(string email)
        {
            try
            {
                // MAKE A LIST OF PARAMETERS
                List<SqlParameter> parameters = new List<SqlParameter>();

                // MAKE PARAMETER
                SqlParameter parameter = new SqlParameter();
                parameter.DbType = DbType.String;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_email";
                parameter.Value = email;
                parameters.Add(parameter);

                return ExecuteDataSetWithCommonQuery(Queries.SELECTUSERSBYEMAIL, parameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region INSERT METHODS
        public int InsertUser(string Firstname, string Givenname, string Nickname, string Email, string Password,
                    DateTime? Dob, Int64 SecretquestionId, string Secretanswer)
        {
            try
            {
                // Get the query
                string sQuery = Queries.INSERTUSER;

                // Parameters
                List<SqlParameter> parameters = new List<SqlParameter>();

                SqlParameter parameter = new SqlParameter();
                parameter = new SqlParameter();
                parameter.DbType = DbType.String;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_firstname";
                parameter.Value = Firstname;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.String;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_givenname";
                parameter.Value = Givenname;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.String;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_nickname";
                parameter.Value = Nickname;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.String;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_email";
                parameter.Value = Email;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.String;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_password";
                parameter.Value = Password;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.Int64;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_secretquestion_id";
                parameter.Value = SecretquestionId;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.String;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_secretanswer";
                parameter.Value = Secretanswer;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.DateTime;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_dob";
                if (Dob == null)
                    parameter.Value = DBNull.Value;
                else
                    parameter.Value = Dob;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.DateTime;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_creationdate";
                parameter.Value = DateTime.Now;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.DateTime;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_activationdate";
                parameter.Value = DateTime.Now;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.DateTime;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_modifieddate";
                parameter.Value = DateTime.Now;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.DateTime;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_lockeddate";
                parameter.Value = DBNull.Value;
                parameters.Add(parameter);           

                // Insert
                int user_id = ExecuteNonQueryWithCommonQuery(sQuery, parameters, true);
                if (user_id > 0)
                {
                    user_id = Convert.ToInt32(ExecuteScalarWithCommonQuery<decimal>(Queries.SELECTIDENTITYAFTERINSERTSQLSERVER));
                }
                return user_id;

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }
        #endregion

        #region UPDATE METHODS
        public int UpdateUser(Int64? Id, string Firstname, string Givenname, string Nickname, string Email, string Password, 
                    DateTime? Dob, Int64 SecretquestionId, string Secretanswer)
        {
            try
            {
                // Check for ID
                if (Id == null)
                    throw new Exception("UpdateUser: no ID given!");

                // Get the query
                string sQuery = Queries.UPDATEUSERWITHPASSWORD;
                if (Password == String.Empty)
                {
                    sQuery = Queries.UPDATEUSERWITHOUTPASSWORD;
                }

                // Parameters
                List<SqlParameter> parameters = new List<SqlParameter>();

                SqlParameter parameter = new SqlParameter();
                parameter = new SqlParameter();
                parameter.DbType = DbType.Int64;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_id";
                parameter.Value = Id;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.String;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_firstname";
                parameter.Value = Firstname;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.String;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_givenname";
                parameter.Value = Givenname;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.String;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_nickname";
                parameter.Value = Nickname;
                parameters.Add(parameter);
                
                parameter = new SqlParameter();
                parameter.DbType = DbType.String;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_email";
                parameter.Value = Email;
                parameters.Add(parameter);

                if (Password != String.Empty)
                {
                    parameter = new SqlParameter();
                    parameter.DbType = DbType.String;
                    parameter.Direction = ParameterDirection.Input;
                    parameter.ParameterName = "user_password";
                    parameter.Value = Password;
                    parameters.Add(parameter);
                }

                parameter = new SqlParameter();
                parameter.DbType = DbType.Int64;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_secretquestion_id";
                parameter.Value = SecretquestionId;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.String;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_secretanswer";
                parameter.Value = Secretanswer;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.DateTime;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_dob";
                if (Dob == null)
                    parameter.Value = DBNull.Value;
                else
                    parameter.Value = Dob;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.DateTime;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_modifieddate";
                parameter.Value = DateTime.Now;
                parameters.Add(parameter);

                // Save
                return ExecuteNonQueryWithCommonQuery(sQuery, parameters, true);

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }

        public int UpdateTokens(Int64? Id, int Tokens)
        {
            try
            {
                // Check for ID
                if (Id == null)
                    throw new Exception("UpdateUser: no ID given!");
                
                // Get the query
                string sQuery = Queries.UPDATEUSERTOKENS;

                // Parameters
                List<SqlParameter> parameters = new List<SqlParameter>();

                SqlParameter parameter = new SqlParameter();
                parameter = new SqlParameter();
                parameter.DbType = DbType.Int64;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_id";
                parameter.Value = Id;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.Int32;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_tokens";
                parameter.Value = Tokens;
                parameters.Add(parameter);

                // Save
                return ExecuteNonQueryWithCommonQuery(sQuery, parameters, true);

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }
        #endregion

        #region VALIDATION
        //VALIDATE USER
        public bool ValidateUser(string username, string password)
        {
            try
            {
                string sQuery = @"
                    SELECT COUNT(user_id)
                    FROM Users WITH(NOLOCK)
                    WHERE user_email = @username
                    AND user_password = @password
                    AND user_lockeddate is NULL";

                //MAKE SOME PARAMETERS
                List<SqlParameter> parameters = new List<SqlParameter>();
                SqlParameter parameter = new SqlParameter();
                parameter.DbType = DbType.String;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "username";
                parameter.Value = username;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.String;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "password";
                parameter.Value = password;
                parameters.Add(parameter);

                int count = (int)ExecuteScalarWithCommonQuery<Int32>(sQuery, parameters);
                if (count == 0)
                {
                    return false;
                }
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }

        //VALIDATE USER
        public bool CheckIfUserExistsByUserName(string username)
        {
            try
            {
                string sQuery = @"
                    SELECT COUNT(user_id)
                    FROM Users WITH(NOLOCK)
                    WHERE user_email = @username";

                //MAKE SOME PARAMETERS
                List<SqlParameter> parameters = new List<SqlParameter>();
                SqlParameter parameter = new SqlParameter();
                parameter.DbType = DbType.String;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "username";
                parameter.Value = username;
                parameters.Add(parameter);

                int count = (int)ExecuteScalarWithCommonQuery<Int32>(sQuery, parameters);
                if (count == 0)
                {
                    return false;
                }
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }
        #endregion

    }
}